﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Public;

namespace DAL
{
    public class ProductNameDAL
    {
        DBConnection conn = new DBConnection();
        public DataSet GetAllProNameCBX()
        {
            return conn.LoadDBComBox("SELECT * FROM ProductName");
        }

        public DataTable searchProName(string searchValue)
        {
            return conn.loadData("SELECT pn.productName AS [Product name], pt.typeName AS [Type], pn.proNameId  " +
            "FROM ProductName pn " +
            "JOIN ProductType pt " +
            "ON pn.typeId = pt.typeId " +
            "WHERE pn.productName LIKE '%" + searchValue + "%'");
        }

        public DataTable getTypeName(int proNameId)
        {
            return conn.loadData("SELECT pt.typeName FROM ProductName pn, ProductType pt " +
            "WHERE pn.typeId = pt.typeId AND pn.proNameId = " + proNameId);
        }

        public DataTable getProNameId(string proName)
        {
            return conn.loadData("SELECT proNameId FROM ProductName WHERE productName = " + proName);
        }

        public int AddProductName(ProductName proName)
        {
            int Nparameter = 2;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@productName"; value[0] = proName.PName;
            name[1] = "@typeId"; value[1] = proName.TypeId;
            return conn.Update("INSERT INTO ProductName(productName, typeId) VALUES (@productName,@typeId)",
                                    name, value, Nparameter);
        }

        public int UpdateProductName(ProductName proName)
        {
            int Nparameter = 3;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@productName"; value[0] = proName.PName;
            name[1] = "@typeId"; value[1] = proName.TypeId;
            name[2] = "@proNameId"; value[2] = proName.PNameId;
            return conn.Update("UPDATE ProductName SET productName = @productName, typeId = @typeId WHERE proNameId = @proNameId",
                                    name, value, Nparameter);
        }
    }
}
